package com.deng.common;


import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.logicalcobwebs.proxool.ProxoolFacade;
import org.logicalcobwebs.proxool.admin.SnapshotIF;

import com.deng.util.DateTools;
//import com.jolbox.bonecp.BoneCPDataSource;

/**
 * @author Deng
 * java数据库帮助类
 */
public class  SQLHelper {
//	private final static String _URL = "jdbc:sqlserver://localhost:1433";
//	private final static String _DB_NAME = "PZSYYDB";
//	private final static String _DB_USER = "performance";
//	private final static String _DB_PW = "performance";
	private final static String _DEFAULT_POOL_NAME = "SQLDB";
	protected final static String _FAILED = "_FAILED";
	protected final static String _SUCCEEDED = "_SUCCEEDED";
	private static int activeCount =0;
//	protected Object getFunction(int organizationId,Date date,String nm){
//		try{
//			String sql = "select dbo."+nm+"("+organizationId+
//			",'"+DateTools.dtsWithTimeT0(date)+"')";
//			System.out.println("调用通用方法：   "+sql);
//			ResultSet rs = queryData(sql);
//			while(rs.next()){				
//				return rs.getObject(1);
//			}
//		}catch(Exception ex){
//			ex.printStackTrace();	
//		}
//		return null;		
//	}
//	protected Object getFunction(int organizationId,String date,String nm){
//		try{
//			String sql = "select dbo."+nm+"("+organizationId+
//			",'"+date+"')";
//			System.out.println("调用通用方法：   "+sql);
//			ResultSet rs = queryData(sql);
//			while(rs.next()){				
//				return rs.getObject(1);
//			}
//		}catch(Exception ex){
//			ex.printStackTrace();	
//		}
//		return null;		
//	}
//	protected Object getFunction(int organizationId,String nm){
//		try{
//			String sql = "select dbo."+nm+"("+organizationId+")";
//			System.out.println("调用通用方法：   "+sql);
//			ResultSet rs = queryData(sql);
//			while(rs.next()){				
//				return rs.getObject(1);
//			}
//		}catch(Exception ex){
//			ex.printStackTrace();	
//		}
//		return null;		
//	}
	protected int getNewMaxId(Connection conn,String tableName){
		int x = 0;
		try{
			String sql = "select max(id) from "+tableName;
			ResultSet rs = queryData(conn,sql);			
			while(rs.next()){
				x = rs.getInt(1);
				break;
			}
//			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return x+1;
	}
	protected ResultSet queryData(Connection conn,String sql){
		System.out.println("::::queryData:::::   "+sql);
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			return rs;
		}catch(Exception ex){
			ex.printStackTrace();	
		}
		return null;
	}
	protected int updateData(Connection conn,String sql){
		System.out.println("::::updateData:::::   "+sql);		
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(sql);
			return pstmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();	
		}
		return 0;
	}
	 
	protected void freeConnection(Connection conn){     
        if(conn!=null){
            try {        
                conn.close();        
            } catch (SQLException e) {                      
                e.printStackTrace();        
            }        
        }        
    }   
	
//	static {
//		try {
//			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//		}catch(ClassNotFoundException ce) {
//			ce.printStackTrace();
//		}
//	}
	 /**      
     * 此方法可以得到连接池的信息      
     * showSnapshotInfo      
     */       
    private void showSnapshotInfo(){        
        try{        
            SnapshotIF snapshot = ProxoolFacade.getSnapshot(_DEFAULT_POOL_NAME, true);        
            int curActiveCount=snapshot.getActiveConnectionCount();//获得活动连接数        
            int availableCount=snapshot.getAvailableConnectionCount();//获得可得到的连接数        
            int maxCount=snapshot.getMaximumConnectionCount() ;//获得总连接数        
            if(curActiveCount!=activeCount)//当活动连接数变化时输出的信息        
            {        
             System.out.println("活动连接数:"+curActiveCount+"(active)  可得到的连接数:"+availableCount+"(available)  总连接数:"+maxCount+"(max)");                     
             activeCount=curActiveCount;        
            }        
        }catch(Exception e){        
            e.printStackTrace();        
        }        
    }        
    
//    protected Connection getConnect() {//BoneCP的链接
//		 try{        
//			 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//			 BoneCPDataSource ds = new BoneCPDataSource(); 
//			 ds.setJdbcUrl("jdbc:sqlserver://localhost:1433;DatabaseName=PZSYYDB");
//			 ds.setUsername("performance"); 
//			 ds.setPassword("performance"); 
//			 Connection conn = ds.getConnection();//DriverManager.getConnection("proxool."+_DEFAULT_POOL_NAME);     
//	           //此处的DBPool是在proxool.xml中配置的连接池别名       
////	            showSnapshotInfo();        
//			 return conn;        
//		 }catch(Exception ex){        
//			 ex.printStackTrace();        
//		 }        
//		 return null;      
////		try {
////			Connection con=DriverManager.
////				getConnection(_URL+";DatabaseName="+_DB_NAME,_DB_USER,_DB_PW);
////			return con;
////		} catch (Exception e) {
////			e.printStackTrace();			
////		}
////		return null;
//	}
	
	protected Connection getConnect() {//Proxool的链接
		 try{        
			 Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");//proxool驱动类        
			 Connection conn = DriverManager.getConnection("proxool."+_DEFAULT_POOL_NAME);     
	           //此处的DBPool是在proxool.xml中配置的连接池别名       
	            showSnapshotInfo();        
			 return conn;        
		 }catch(Exception ex){        
			 ex.printStackTrace();        
		 }        
		 return null;      
//		try {
//			Connection con=DriverManager.
//				getConnection(_URL+";DatabaseName="+_DB_NAME,_DB_USER,_DB_PW);
//			return con;
//		} catch (Exception e) {
//			e.printStackTrace();			
//		}
//		return null;
	}
	 
//	public static Timestamp getTimestamp(Date date){
////		d(date.toLocaleString());
//		return Timestamp.valueOf(format.format(date));
//	}

	protected String convertDataToString(Object ob){
		if(ob == null){
			return "";
		}else if(ob instanceof BigDecimal){
			return ob.toString();
		}else if(ob instanceof Float){
			return new BigDecimal((Float)ob).setScale(4, BigDecimal.ROUND_HALF_UP).toString();
		}else if(ob instanceof Date){
			return DateTools.dtsJustDate((Date)ob);
		}else{
			return ob+"";
		}
	}
	
}

